/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/

SET NOCOUNT ON
GO

PRINT 'Dropping the database'
GO

USE master
GO
if exists (select * from sysdatabases where name='MVPNorthwind')
		drop database MVPNorthwind
go

PRINT 'Creating the Database'
EXECUTE (N'CREATE DATABASE MVPNorthwind
  ON PRIMARY (NAME = N''MVPNorthwind'', FILENAME = N''D:\development\databases\MVPNorthwind.mdf'')
  LOG ON (NAME = N''@INITIAL_CATALOT@_log'',  FILENAME = N''D:\development\databases\MVPNorthwind.ldf'')')
go

exec sp_dboption 'MVPNorthwind','trunc. log on chkpt.','true'
exec sp_dboption 'MVPNorthwind','select into/bulkcopy','true'
GO




set quoted_identifier on
GO

/* Set DATEFORMAT so that the date strings are interpreted correctly regardless of
   the default DATEFORMAT on the server.
*/
SET DATEFORMAT mdy
GO

PRINT 'Adding access to ASP.Net'
use [MVPNorthwind]

if not exists (select * from sysusers where name = N'BITWISEMASTER\ASPNET' and uid < 16382)
	EXEC sp_grantdbaccess N'BITWISEMASTER\ASPNET', N'BITWISEMASTER\ASPNET'
GO

if not exists (select * from sysusers where name = N'WebUser' and uid > 16399)
	EXEC sp_addrole N'WebUser'
GO

exec sp_addrolemember N'WebUser', N'BITWISEMASTER\ASPNET'
GO

PRINT 'Creating the Employees Table'
go
CREATE TABLE Employees (
	EmployeeID int IDENTITY (1, 1) NOT NULL ,
	LastName nvarchar (20) NOT NULL ,
	FirstName nvarchar (10) NOT NULL ,
	Title nvarchar (30) NULL ,
	TitleOfCourtesy nvarchar (25) NULL ,
	BirthDate datetime NULL ,
	HireDate datetime NULL ,
	Address nvarchar (60) NULL ,
	City nvarchar (15) NULL ,
	Region nvarchar (15) NULL ,
	PostalCode nvarchar (10) NULL ,
	Country nvarchar (15) NULL ,
	HomePhone nvarchar (24) NULL ,
	Extension nvarchar (4) NULL ,
	Photo image NULL ,
	Notes ntext NULL ,
	ReportsTo int NULL ,
	PhotoPath nvarchar (255) NULL ,
	CONSTRAINT PK_Employees PRIMARY KEY  CLUSTERED 
	(
		EmployeeID
	),
	CONSTRAINT FK_Employees_Employees FOREIGN KEY 
	(
		ReportsTo
	) REFERENCES dbo.Employees (
		EmployeeID
	),
	/*CONSTRAINT CK_Birthdate CHECK (BirthDate < getdate())*/
)
GO
 CREATE  INDEX LastName ON dbo.Employees(LastName)
GO
 CREATE  INDEX PostalCode ON dbo.Employees(PostalCode)
GO

PRINT 'Creating the Categories Table'
CREATE TABLE Categories (
	CategoryID int IDENTITY (1, 1) NOT NULL ,
	CategoryName nvarchar (15) NOT NULL ,
	Description ntext NULL ,
	Picture image NULL ,
	CONSTRAINT PK_Categories PRIMARY KEY  CLUSTERED 
	(
		CategoryID
	)
)
GO
 CREATE  INDEX CategoryName ON dbo.Categories(CategoryName)
GO

PRINT 'Creating the Customers Table'
CREATE TABLE Customers (
	CustomerId int IDENTITY(1,1) NOT NULL,
	CustomerMnemonic nvarchar (5) NOT NULL ,
	CompanyName nvarchar (40) NOT NULL ,
	ContactName nvarchar (30) NULL ,
	ContactTitle nvarchar (30) NULL ,
	Address nvarchar (60) NULL ,
	City nvarchar (15) NULL ,
	Region nvarchar (15) NULL ,
	PostalCode nvarchar (10) NULL ,
	Country nvarchar (15) NULL ,
	Phone nvarchar (24) NULL ,
	Fax nvarchar (24) NULL ,
	CONSTRAINT PK_Customers PRIMARY KEY  CLUSTERED 
	(
		CustomerID
	)
)
GO
 CREATE  INDEX City ON dbo.Customers(City)
GO
 CREATE  INDEX CompanyName ON dbo.Customers(CompanyName)
GO
 CREATE  INDEX PostalCode ON dbo.Customers(PostalCode)
GO
 CREATE  INDEX Region ON dbo.Customers(Region)
GO

PRINT 'Creating the Shippers Table'
CREATE TABLE Shippers (
	ShipperID int IDENTITY (1, 1) NOT NULL ,
	CompanyName nvarchar (40) NOT NULL ,
	Phone nvarchar (24) NULL ,
	CONSTRAINT PK_Shippers PRIMARY KEY  CLUSTERED 
	(
		ShipperID
	)
)
GO

PRINT 'Creating the Suppliers Table'
CREATE TABLE Suppliers (
	SupplierID int IDENTITY (1, 1) NOT NULL ,
	CompanyName nvarchar (40) NOT NULL ,
	ContactName nvarchar (30) NULL ,
	ContactTitle nvarchar (30) NULL ,
	Address nvarchar (60) NULL ,
	City nvarchar (15) NULL ,
	Region nvarchar (15) NULL ,
	PostalCode nvarchar (10) NULL ,
	Country nvarchar (15) NULL ,
	Phone nvarchar (24) NULL ,
	Fax nvarchar (24) NULL ,
	HomePage ntext NULL ,
	CONSTRAINT PK_Suppliers PRIMARY KEY  CLUSTERED 
	(
		SupplierID
	)
)
GO
 CREATE  INDEX CompanyName ON dbo.Suppliers(CompanyName)
GO
 CREATE  INDEX PostalCode ON dbo.Suppliers(PostalCode)
GO

PRINT 'Creating the Orders Table'
CREATE TABLE Orders (
	OrderID int IDENTITY (1, 1) NOT NULL ,
	CustomerID int NOT NULL ,
	EmployeeID int NULL ,
	OrderDate datetime NULL ,
	RequiredDate datetime NULL ,
	ShippedDate datetime NULL ,
	ShipVia int NULL ,
	Freight money NULL CONSTRAINT DF_Orders_Freight DEFAULT (0),
	ShipName nvarchar (40) NULL ,
	ShipAddress nvarchar (60) NULL ,
	ShipCity nvarchar (15) NULL ,
	ShipRegion nvarchar (15) NULL ,
	ShipPostalCode nvarchar (10) NULL ,
	ShipCountry nvarchar (15) NULL ,
	CONSTRAINT PK_Orders PRIMARY KEY  CLUSTERED 
	(
		OrderID
	),
	CONSTRAINT FK_Orders_Customers FOREIGN KEY 
	(
		CustomerID
	) REFERENCES dbo.Customers (
		CustomerID
	),
	CONSTRAINT FK_Orders_Employees FOREIGN KEY 
	(
		EmployeeID
	) REFERENCES dbo.Employees (
		EmployeeID
	),
	CONSTRAINT FK_Orders_Shippers FOREIGN KEY 
	(
		ShipVia
	) REFERENCES dbo.Shippers (
		ShipperID
	)
)
GO
 CREATE  INDEX CustomerID ON dbo.Orders(CustomerID)
GO
 CREATE  INDEX CustomersOrders ON dbo.Orders(CustomerID)
GO
 CREATE  INDEX EmployeeID ON dbo.Orders(EmployeeID)
GO
 CREATE  INDEX EmployeesOrders ON dbo.Orders(EmployeeID)
GO
 CREATE  INDEX OrderDate ON dbo.Orders(OrderDate)
GO
 CREATE  INDEX ShippedDate ON dbo.Orders(ShippedDate)
GO
 CREATE  INDEX ShippersOrders ON dbo.Orders(ShipVia)
GO
 CREATE  INDEX ShipPostalCode ON dbo.Orders(ShipPostalCode)
GO

PRINT 'Creating the Products Table'
CREATE TABLE Products (
	ProductID int IDENTITY (1, 1) NOT NULL ,
	ProductName nvarchar (40) NOT NULL ,
	SupplierID int NULL ,
	CategoryID int NULL ,
	QuantityPerUnit nvarchar (20) NULL ,
	UnitPrice money NULL CONSTRAINT DF_Products_UnitPrice DEFAULT (0),
	UnitsInStock smallint NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT (0),
	UnitsOnOrder smallint NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT (0),
	ReorderLevel smallint NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT (0),
	Discontinued bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT (0),
	CONSTRAINT PK_Products PRIMARY KEY  CLUSTERED 
	(
		ProductID
	),
	CONSTRAINT FK_Products_Categories FOREIGN KEY 
	(
		CategoryID
	) REFERENCES dbo.Categories (
		CategoryID
	),
	CONSTRAINT FK_Products_Suppliers FOREIGN KEY 
	(
		SupplierID
	) REFERENCES dbo.Suppliers (
		SupplierID
	),
	/*CONSTRAINT CK_Products_UnitPrice CHECK (UnitPrice >= 0),
	CONSTRAINT CK_ReorderLevel CHECK (ReorderLevel >= 0),
	CONSTRAINT CK_UnitsInStock CHECK (UnitsInStock >= 0),
	CONSTRAINT CK_UnitsOnOrder CHECK (UnitsOnOrder >= 0)*/
)
GO
 CREATE  INDEX CategoriesProducts ON dbo.Products(CategoryID)
GO
 CREATE  INDEX CategoryID ON dbo.Products(CategoryID)
GO
 CREATE  INDEX ProductName ON dbo.Products(ProductName)
GO
 CREATE  INDEX SupplierID ON dbo.Products(SupplierID)
GO
 CREATE  INDEX SuppliersProducts ON dbo.Products(SupplierID)
GO

PRINT 'Creating the OrderDetails Table'
CREATE TABLE OrderDetails (
	OrderID int NOT NULL ,
	ProductID int NOT NULL ,
	UnitPrice money NOT NULL CONSTRAINT DF_Order_Details_UnitPrice DEFAULT (0),
	Quantity smallint NOT NULL CONSTRAINT DF_Order_Details_Quantity DEFAULT (1),
	Discount real NOT NULL CONSTRAINT DF_Order_Details_Discount DEFAULT (0),
	CONSTRAINT PK_OrderDetails PRIMARY KEY  CLUSTERED 
	(
		OrderID,
		ProductID
	),
	CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY 
	(
		OrderID
	) REFERENCES dbo.Orders (
		OrderID
	),
	CONSTRAINT FK_OrderDetails_Products FOREIGN KEY 
	(
		ProductID
	) REFERENCES dbo.Products (
		ProductID
	),
	/*CONSTRAINT CK_Discount CHECK (Discount >= 0 and (Discount <= 1)),
	CONSTRAINT CK_Quantity CHECK (Quantity > 0),
	CONSTRAINT CK_UnitPrice CHECK (UnitPrice >= 0)*/
)
GO
 CREATE  INDEX OrderID ON dbo.OrderDetails(OrderID)
GO
 CREATE  INDEX Orders_OrderDetails ON dbo.OrderDetails(OrderID)
GO
 CREATE  INDEX ProductID ON dbo.OrderDetails(ProductID)
GO
 CREATE  INDEX Products_OrderDetails ON dbo.OrderDetails(ProductID)
GO

PRINT 'Creating the CustomerCustomerDemo Table'
CREATE TABLE [dbo].[CustomerCustomerDemo] 
	([CustomerID] int NOT NULL,
	[CustomerTypeID] int NOT NULL
) ON [PRIMARY] 
GO

PRINT 'Creating the CustomerDemographics Table'
CREATE TABLE [dbo].[CustomerDemographics] 
	([CustomerTypeID] int NOT NULL ,
	[CustomerDesc] [ntext] NULL 
)  ON [PRIMARY] 
GO		
	
PRINT 'Creating the Region Table'
CREATE TABLE [dbo].[Region] 
	( [RegionID] [int] IDENTITY (1,1) NOT NULL ,
	  [RegionMnemonic] [nvarchar] (5) NOT NULL,
	[RegionDescription] [nchar] (50) NOT NULL 
) ON [PRIMARY]
GO

PRINT 'Creating the Territories Table'
CREATE TABLE [dbo].[Territories] 
	([TerritoryID] int IDENTITY(1,1) NOT NULL ,
	 [TerritoryMnemonic] [nvarchar] (5) NOT NULL,
	[TerritoryDescription] [nchar] (50) NOT NULL ,
        [RegionID] [int] NOT NULL
) ON [PRIMARY]
GO

PRINT 'Creating the EmployeeTerritories Table'
CREATE TABLE [dbo].[EmployeeTerritories] 
	([EmployeeID] [int] NOT NULL,
	[TerritoryID] [int] NOT NULL 
) ON [PRIMARY]


PRINT 'Adding constraints for the CustomerDemographics Table'
ALTER TABLE CustomerDemographics
	ADD CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerTypeID]
	) ON [PRIMARY]
GO

PRINT 'Adding constraints for the CustomerCustomerDemo Table'
ALTER TABLE CustomerCustomerDemo
	ADD CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerID],
		[CustomerTypeID]
	) ON [PRIMARY],
	CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY 
	(
		[CustomerTypeID]
	) REFERENCES [dbo].[CustomerDemographics] (
		[CustomerTypeID]
	),
	CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
	(
		[CustomerID]
	) REFERENCES [dbo].[Customers] (
		[CustomerID]
	)
GO


PRINT 'Adding constraints for the Region Table'
ALTER TABLE Region
	ADD CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED 
	(
		[RegionID]
	)  ON [PRIMARY] 
GO

PRINT 'Adding constraints for the Territories Table'
ALTER TABLE Territories
	ADD CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED 
	(
		[TerritoryID]
	)  ON [PRIMARY] 
	,
	CONSTRAINT [FK_Territories_Region] FOREIGN KEY 
	(
		[RegionID]
	) REFERENCES [dbo].[Region] (
		[RegionID]
	)
GO

PRINT 'Adding constraints for the EmployeeTerritories Table'
ALTER TABLE EmployeeTerritories
	ADD CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED 
	(
		[EmployeeID],
		[TerritoryID]
	) ON [PRIMARY]
	,
	CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [dbo].[Employees] (
		[EmployeeID]
	)
	,
	CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY 
	(
		[TerritoryID]
	) REFERENCES [dbo].[Territories] (
		[TerritoryID]
	)
GO
